﻿//************************************************
//
//      Project Dong Anh - Marketing Online
//      © Copyright DCT - 08/2011
//------------------------------------------------
//      Developer: ((¯¨¤»- MXT-«¤¨¯))
//************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using Utils;
using DCSLibrary;
using BDS;

namespace DCTLib.Common
{
    public class SQLHelper : IDisposable
    {        
        //strConnectionString
        private const string ConnectionStringKey = "DCSConnectionString";
        private SqlCommand cmd = new SqlCommand();
        private SqlConnection conn = new SqlConnection();
        private bool _CatchError = true;
        
        public SQLHelper()
        {
            cmd.CommandType = CommandType.StoredProcedure;
        }

        private void InitCommand()
        {
            conn.ConnectionString = SQLConnectionString;
            conn.Open();
            cmd.Connection = conn;
        }

        public void Dispose()
        {
            cmd.Dispose();
            if (conn != null)
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
                conn.Dispose();
            }
        }

        private string SQLConnectionString
        {
            get
            {
                //return  ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
                return Security.Decode(ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString);
            }
        }

        public bool CatchError
        {
            get { return _CatchError; }
            set { _CatchError = value; }
        }

        public IDataParameterCollection Parameters
        {
            get
            {
                return cmd.Parameters;
            }
        }

        public void AddParameter(string paramname, object paramvalue)
        {
            SqlParameter param = new SqlParameter(paramname, paramvalue);
            cmd.Parameters.Add(param);
        }

        public void AddParameter(IDataParameter param)
        {
            cmd.Parameters.Add(param);
        }

        public void SwitchCommandType(CommandType commandType)
        {
            cmd.CommandType = commandType;
        }

        public DataTable GetDataTable(string pstrSQL)
        {
            DataTable rsData = new DataTable();
            SqlDataAdapter adpAdapter = new SqlDataAdapter();

            try
            {
                InitCommand();
                cmd.CommandText = pstrSQL;
                adpAdapter.SelectCommand = cmd;
                adpAdapter.Fill(rsData);

                return rsData;
            }
            catch (SqlException ex)
            {
                if (this.CatchError)
                    return null;
                else
                    throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public bool ExecuteSQL(string pstrSQL)
        {
            try
            {
                InitCommand();
                cmd.CommandText = pstrSQL;
                cmd.ExecuteNonQuery();

                return true;
            }
            catch (SqlException ex)
            {
                if (this.CatchError)
                    return false;
                else
                    throw;
            }
            finally
            {
                CloseConnection();
            }
        }

        public object ExecuteScalar(string pstrSQL)
        {
            try
            {
                InitCommand();
                cmd.CommandText = pstrSQL;
                return cmd.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                if (this.CatchError)
                    return null;
                else
                    throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// USING FOR TESTING>>>>>>>>>>>>>>>>>>>>>>>>>>>NOTEONONONONO
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public static object TExecuteScalar(string Sql)
        {
            SqlConnection Connection = new SqlConnection(Security.Decode(ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString));
            SqlCommand Command = new SqlCommand(Sql, Connection);
            object result = null;
            try
            {
                Connection.Open();
                result = Command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Connection.Close();
            }
            return result;
        }
        /// <summary>
        /// TESTING .........................NO USE
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetAll(string sql)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(Security.Decode(ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString));
            SqlCommand dbCmd = new SqlCommand(sql, dbConn);
            dbCmd.CommandType = CommandType.Text;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        private void CloseConnection()
        {
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }
    }
}
